/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.CarOrderDetailDTO;
import east2west.interfaces.CarOrderDetailInterface;
import east2west.util.DatabaseUtil;
import east2west.util.DateTimeUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author anhtuan
 */
public class CarOrderDetailDAO implements CarOrderDetailInterface {

    Connection con = null;
    CarOrderDetailDTO carorderdetai = null;
    CallableStatement call = null;
    ResultSet rs = null;
    private final String SQL_READ_BY_ALL = "{call sp_Call_View_CarOrderDetail}";
    private final String SQL_READ_BY_ID = "{call sp_Call_View_CarOrderDetail_FindId (?)}";
    private final String SQL_READ_BY_ORDERID = "select * from V_CarOrderDetail where OrderId = ?";
    private final String SQL_READ_BY_CARID = "{call sp_Call_View_CarOrderDetail_FindCarId (?)}";
    private final String SQL_READ_BY_PICKUPDATE = "{call sp_Call_View_CarOrderDetail_FindPickUpDate (?)}";
    private final String SQL_READ_BY_DROPOFFDATE = "{call sp_Call_View_CarOrderDetail_FindDropOffDate (?)}";
    private final String SQL_READ_BY_DRIVER = "{call sp_Call_View_CarOrderDetail_FindDriver (?)}";
    private final String SQL_READ_BY_TOTAL = "{call sp_Call_View_CarOrderDetail_FindTotal (?)}";
    private final String SQL_CREATE = "{call sp_Insert_CarOderDetail (?,?,?,?,?,?,?)}";
    private final String SQL_UPDATE = "{call sp_Update_CarOderDetail (?,?,?,?,?,?,?,?)}";
    private final String SQL_DELETE = "{call sp_Delete_CarOderDetail (?)}";
//    private final String SQL_DELETE_CARID = "{call sp_Delete_CarDetail_CarId (?)}";

    public ArrayList<CarOrderDetailDTO> readByAll() {
        ArrayList<CarOrderDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_ALL);
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDetailDTO>();
            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                if (rs.getDate(4) == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(rs.getDate(4), "dd-MM-yyyy"));
                }
                if (rs.getDate(5) == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(rs.getDate(5), "dd-MM-yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
                list.add(carorderdetai);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public CarOrderDetailDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_ID);
            call.setInt(1, id);
            rs = call.executeQuery();

            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                if (rs.getDate(4) == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(rs.getDate(4), "dd-MM-yyyy"));
                }
                if (rs.getDate(5) == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(rs.getDate(5), "dd-MM-yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
            }
            rs.close();
            call.close();
            return carorderdetai;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDetailDTO> findByOrderId(int id) {
        ArrayList<CarOrderDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_ORDERID);
            call.setInt(1, id);
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDetailDTO>();
            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                if (rs.getDate(4) == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(rs.getDate(4), "MM/dd/yyyy"));
                }
                if (rs.getDate(5) == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(rs.getDate(5), "MM/dd/yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
                list.add(carorderdetai);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDetailDTO> findByCarId(int id) {
        ArrayList<CarOrderDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_CARID);
            call.setInt(1, id);
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDetailDTO>();
            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                if (rs.getDate(4) == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(rs.getDate(4), "MM/dd/yyyy"));
                }
                if (rs.getDate(5) == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(rs.getDate(5), "MM/dd/yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
                list.add(carorderdetai);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDetailDTO> findByPickUpDate(String pickUpDate) {
        ArrayList<CarOrderDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_PICKUPDATE);
            call.setDate(1, DateTimeUtil.convert2Date(pickUpDate, "dd-MM-yyyy"));
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDetailDTO>();
            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                if (rs.getDate(4) == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(rs.getDate(4), "dd-MM-yyyy"));
                }
                if (rs.getDate(5) == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(rs.getDate(5), "dd-MM-yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
                list.add(carorderdetai);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDetailDTO> findByDropOffDate(String dropOffDate) {
        ArrayList<CarOrderDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_DROPOFFDATE);
            call.setDate(1, DateTimeUtil.convert2Date(dropOffDate, "dd-MM-yyyy"));
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDetailDTO>();
            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                if (rs.getDate(4) == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(rs.getDate(4), "dd-MM-yyyy"));
                }
                if (rs.getDate(5) == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(rs.getDate(5), "dd-MM-yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
                list.add(carorderdetai);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDetailDTO> findByDriver(byte driver) {
        ArrayList<CarOrderDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_DRIVER);
            call.setByte(1, driver);
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDetailDTO>();
            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                if (rs.getDate(4) == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(rs.getDate(4), "dd-MM-yyyy"));
                }
                if (rs.getDate(5) == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(rs.getDate(5), "dd-MM-yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
                list.add(carorderdetai);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDetailDTO> findByTotal(float total) {
        ArrayList<CarOrderDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_TOTAL);
            call.setFloat(1, total);
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDetailDTO>();
            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                if (rs.getDate(4) == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(rs.getDate(4), "dd-MM-yyyy"));
                }
                if (rs.getDate(5) == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(rs.getDate(5), "dd-MM-yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
                list.add(carorderdetai);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public boolean create(CarOrderDetailDTO dto) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall(SQL_CREATE);
            call.setInt(1, dto.getOderId());
            call.setInt(2, dto.getCarId());
            call.setString(3, dto.getPickUpdate());
            call.setString(4, dto.getDropOffDate());
            if (dto.getDriver() == 1) {
                call.setBoolean(5, true);
            } else {
                call.setBoolean(5, false);
            }
            call.setFloat(6, dto.getTotal());
            call.setInt(7, dto.getQty());
            call.executeUpdate();
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarOrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean update(CarOrderDetailDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_UPDATE);
            call.setInt(1, dto.getCarOderDetailId());
            call.setInt(2, dto.getOderId());
            call.setInt(3, dto.getCarId());
            call.setDate(4, DateTimeUtil.convert2Date(dto.getPickUpdate(), "dd-MM-yyyy"));
            call.setDate(5, DateTimeUtil.convert2Date(dto.getDropOffDate(), "dd-MM-yyyy"));
            call.setByte(6, dto.getDriver());
            call.setFloat(7, dto.getTotal());
            call.setInt(8, dto.getQty());

            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarOrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean delete(CarOrderDetailDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_DELETE);
            call.setInt(1, id);
            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarOrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public ArrayList<CarOrderDetailDTO> findByOrderDetailNotCancel(int orderid) {
        ArrayList<CarOrderDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select * from V_CarOrderDetailCancel where OrderId = ?");
            call.setInt(1, orderid);
            rs = call.executeQuery();

            list = new ArrayList<CarOrderDetailDTO>();
            while (rs.next()) {
                carorderdetai = new CarOrderDetailDTO();
                carorderdetai.setCarOderDetailId(rs.getInt(1));
                carorderdetai.setOderId(rs.getInt(2));
                carorderdetai.setCarId(rs.getInt(3));
                Date pickup = rs.getDate(4);
                if (pickup == null) {
                    carorderdetai.setPickUpdate("");
                } else {
                    carorderdetai.setPickUpdate(DateTimeUtil.convert2String(pickup, "MM/dd/yyyy"));
                }
                Date dropoff = rs.getDate(5);
                if (dropoff == null) {
                    carorderdetai.setDropOffDate("");
                } else {
                    carorderdetai.setDropOffDate(DateTimeUtil.convert2String(dropoff, "MM/dd/yyyy"));
                }
                carorderdetai.setDriver(rs.getByte(6));
                carorderdetai.setTotal(rs.getFloat(7));
                carorderdetai.setQty(rs.getInt(8));
                list.add(carorderdetai);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }
}
